'''
pymysql模块：
    作用：连接MySQL数据库,  执行DML操作
    安装：pip install pymysql

使用流程：
    1、创建数据库连接
    2、基于数据库连接创建游标 cursor
    3、根据业务逻辑执行SQL
    4、关闭游标、数据库连接
'''

import pymysql

# 查询数据
def select_data():
    # 创建数据库连接
    dbconn = pymysql.connect(host="192.168.140.10", user="admin", password="123456", database="it")
    # 创建游标
    cr = dbconn.cursor()

    # 执行SQL
    test_sql = "select ip, user, password, port from hosts"
    result = cr.execute(test_sql)
    print("结果行数: ", result)

    # 获取实际的数据
    hosts = cr.fetchall()
    print(hosts)

    for ip, user, password, port in hosts:
        print("ssh %s@%s -p %s" % (user, ip, port))

    cr.close()
    dbconn.close()

# 写操作
def write_data():
    dbconn = pymysql.connect(host="192.168.140.10", user="admin", password="123456", database="it",port=3306)
    cr = dbconn.cursor()

    # 添加数据
    # insert_sql = "insert into hosts(ip, user, password, port) values('10.1.1.1', 'martin', '123', 5555)"

    # 更新数据
    # update_sql = "update hosts set password='WWW.1.com' where ip='10.1.1.1'"

    # 删除数据
    delete_sql = "delete from hosts where ip='10.1.1.1'"


    result = cr.execute(delete_sql)
    # 提交事务
    dbconn.commit()

    if result != 0:
        print("数据添加成功")
    else:
        print("数据添加失败")

    cr.close()
    dbconn.close()

if __name__ == '__main__':
    write_data()
















